package br.com.gbvbahia.mybooks.dao.contract;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DB extends SQLiteOpenHelper {

	public static final String DB_NAME = "mybooks.db";
	public static final int VERSION = 1;
	public static final int NOT_SINCRONIZADO = 0;
	public static final int SINCRONIZADO = 1;

	public DB(Context context) {
		super(context, DB_NAME, null, VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(getEditoraScriptTable()[0]);
		db.execSQL(getEditoraScriptTable()[1]);
		db.execSQL(getBookrScriptTable());
		db.execSQL(getPrateleiraScriptTable()[0]);
		db.execSQL(getPrateleiraScriptTable()[1]);
		db.execSQL(getLivroPrateleiraScript());
		db.execSQL(getPerfil());
		db.execSQL(getRemovidos());
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// NEW Version
	}

	private String[] getEditoraScriptTable() {
		String[] toReturn = new String[2];
		toReturn[0] = "CREATE TABLE [editoras] ( "
				+ "   [id] VARCHAR(100) NOT NULL ON CONFLICT ROLLBACK, "
				+ " [nome] VARCHAR(100) NOT NULL ON CONFLICT ROLLBACK, "
				+ " [data_alteracao] VARCHAR(50) NOT NULL, "
				+ " [sincronizado] INTEGER NOT NULL DEFAULT 0);";
		toReturn[1] = "CREATE UNIQUE INDEX [uk_nome_editora] ON [editoras] ([nome]);";
		return toReturn;
	}

	private String getBookrScriptTable() {
		return " CREATE TABLE [livros] ( "
				+ " [id] VARCHAR(100) NOT NULL ON CONFLICT ROLLBACK, "
				+ " [nome] VARCHAR(100) NOT NULL ON CONFLICT ROLLBACK, "
				+ " [autor] VARCHAR(100), "
				+ " [edicao] INTEGER DEFAULT 1, "
				+ " [isbn] VARCHAR(20), "
				+ " [descricao] VARCHAR(500), "
				+ " [ano] INTEGER, "
				+ " [status] VARCHAR(20), "
				+ " [nota] INTEGER, "
				+ " [avaliacao] VARCHAR(1000), "
				+ " [tipo] VARCHAR(20), "
				+ " [sincronizado] INTEGER NOT NULL DEFAULT 0, "
				+ " [data_alteracao] VARCHAR(30) NOT NULL, "
				+ " [cd_editora] VARCHAR(100) CONSTRAINT [fk_ediora] REFERENCES"
				+ " [editoras]([id]) ON DELETE SET NULL MATCH SIMPLE,"
				+ " [recomendado] INTEGER NOT NULL DEFAULT 0,"
				+ " [pre_avaliacao] VARCHAR(1000)); ";
	}

	private String[] getPrateleiraScriptTable() {
		String[] toReturn = new String[2];
		toReturn[0] = " CREATE TABLE [prateleira] ( "
				+ " [id] VARCHAR(100) NOT NULL ON CONFLICT ROLLBACK, "
				+ " [nome] VARCHAR(50) NOT NULL ON CONFLICT ROLLBACK, "
				+ " [sincronizado] INTEGER NOT NULL DEFAULT 0, "
				+ " [data_alteracao] VARCHAR(30) NOT NULL, "
				+ " [descricao] VARCHAR(500),"
				+ " [publica] INTEGER NOT NULL DEFAULT 1);";
		toReturn[1] = " CREATE UNIQUE INDEX [uk_nome_prateleira] ON [prateleira] ([nome]);";
		return toReturn;
	}

	private String getLivroPrateleiraScript() {
		return "CREATE TABLE [livro_prateleira] (  "
				+ " [cd_livro] VARCHAR(100) NOT NULL ON CONFLICT ROLLBACK CONSTRAINT "
				+ " [fk_livro_prateleira] REFERENCES [livros]([id]), "
				+ " [sincronizado] INTEGER NOT NULL DEFAULT 0, "
				+ " [cd_prateleira] VARCHAR(100) NOT NULL ON CONFLICT ROLLBACK CONSTRAINT "
				+ " [fk_prateleira_livro] REFERENCES [prateleira]([id]));";
	}

	private String getPerfil() {
		return "CREATE TABLE [perfil] ( "
				+ " [id] VARCHAR(100) NOT NULL ON CONFLICT ROLLBACK, "
				+ " [perfil] VARCHAR(30) NOT NULL ON CONFLICT ROLLBACK,  "
				+ " [data_alteracao] VARCHAR(50) NOT NULL, "
				+ " [senha] VARCHAR(20), "
				+ " [email] VARCHAR(100) NOT NULL ON CONFLICT ROLLBACK, "
				+ " [ws_salvo] INTEGER NOT NULL DEFAULT 0); ";
	}

	private String getRemovidos() {
		return "  CREATE TABLE [removidos] ( "
				+ " [idLivroRemovido] VARCHAR(100) NOT NULL ON CONFLICT ROLLBACK, "
				+ " [dataRemocao] VARCHAR(30) NOT NULL ON CONFLICT ROLLBACK); ";
	}
}
